// lista pracowników

unit Unit11;

{$mode objfpc}{$H+}

interface

uses
  Classes, SysUtils, db, FileUtil, Forms, Controls, Graphics, Dialogs, StdCtrls,
  DBGrids, IBQuery, IBDatabase, ZDataset;

type

  { TForm11 }

  TForm11 = class(TForm)
    Button1: TButton;
    Button2: TButton;
    Button3: TButton;
    Button4: TButton;
    Datasource1: TDatasource;
    DBGrid1: TDBGrid;
    Edit1: TEdit;
    GroupBox1: TGroupBox;
    ibq1: TIBQuery;
    ibq2: TIBQuery;
    ibt1: TIBTransaction;
    ibt2: TIBTransaction;
    procedure Button1Click(Sender: TObject);
    procedure Button2Click(Sender: TObject);
    procedure Button3Click(Sender: TObject);
    procedure Button4Click(Sender: TObject);
    procedure Edit1Change(Sender: TObject);
    procedure FormClose(Sender: TObject; var CloseAction: TCloseAction);
    procedure FormShow(Sender: TObject);
  private
    { private declarations }
  public
    { public declarations }

    procedure Fill_ListaGrup;               // wypelnia kontrolke ComboBox lista grup pracownikow
  end;

var
  Form11: TForm11;

implementation

{$R *.lfm}

uses
  unit12, unit5, unit19;

{ TForm11 }

procedure TForm11.Button4Click(Sender: TObject);
begin
  close;
end;

procedure TForm11.Edit1Change(Sender: TObject);
begin
  FormShow(nil);
end;

procedure TForm11.FormClose(Sender: TObject; var CloseAction: TCloseAction);
begin
  if ibq1.Transaction.Active then ibq1.Transaction.Commit;
end;

procedure TForm11.FormShow(Sender: TObject);
begin
  if ibq1.Active then ibq1.Close;
  ibq1.SQL.Clear;
  if trim(edit1.Text) <> '' then
  begin
    ibq1.SQL.Add('select pr.ean,pr.nazwadl "Pracownik",pr.data_zal,gr.nazwadl "Grupa",pr.pracownik_idx,pr.prac_grup_idx from pracownik pr');
    ibq1.SQL.Add('inner join prac_grup gr on gr.prac_grup_idx = pr.prac_grup_idx');
    ibq1.SQL.Add('where ean like :a order by upper(pr.nazwadl)');
    ibq1.ParamByName('a').AsString:='%'+edit1.Text+'%';
  end
  else
    begin
      ibq1.SQL.Add('select pr.ean,pr.nazwadl "Pracownik",pr.data_zal,gr.nazwadl "Grupa",pr.pracownik_idx,pr.prac_grup_idx from pracownik pr');
      ibq1.SQL.Add('inner join prac_grup gr on gr.prac_grup_idx = pr.prac_grup_idx');
      ibq1.SQL.Add('order by upper(pr.nazwadl)');
    end;
  try
    if ibq1.Transaction.Active then ibq1.Transaction.Commit;
    ibq1.Transaction.StartTransaction;
    ibq1.Open;
  except
    ibq1.Transaction.Rollback;
  end;
end;

procedure TForm11.Fill_ListaGrup;
begin
  form12.ComboBox1.Clear;

  ibq2.SQL.Clear;
  ibq2.SQL.Add('select * from prac_grup order by upper(nazwadl)');
  try
    ibq2.Transaction.StartTransaction;
    ibq2.Open;

    ibq2.First;
    while not ibq2.EOF do
    begin
      form12.ComboBox1.Items.Add(ibq2.FieldByName('nazwadl').AsString+' ('+IntToStr(ibq2.FieldByName('prac_grup_idx').AsInteger)+')');
      ibq2.Next;
    end;

    ibq2.Transaction.Commit;
  except
    ibq2.Transaction.Rollback;
  end;
end;


// nowy
procedure TForm11.Button1Click(Sender: TObject);
var
  key : integer;
begin
  Fill_ListaGrup();
  form12.Caption:='Nowy pracownik';
  form12.Edit1.Clear;
  form12.Edit1.Enabled:=true;
  form12.Edit2.Clear;
  form12.ActiveControl:=form12.Edit1;
  if form12.ShowModal = mrOK then
  begin
    key:=GetIdx(form12.ComboBox1.Text);
    if ibq2.Active then ibq1.Close;
    ibq2.SQL.Clear;
    ibq2.SQL.Add('insert into pracownik (ean, nazwadl, data_zal, prac_grup_idx) values (:a,:b,:c,:d)');
    ibq2.ParamByName('a').AsString:=form12.Edit1.Text;
    ibq2.ParamByName('b').AsString:=form12.Edit2.Text;
    ibq2.ParamByName('c').AsDateTime:=Now(); // data zalozenia, przypisuje bierzaca date
    ibq2.ParamByName('d').AsInteger:=key;
    try
      ibq2.Transaction.StartTransaction;
      ibq2.ExecSQL;
      ibq2.Transaction.Commit;
    except
      ibq2.Transaction.Rollback;
    end;

    FormShow(nil);
  end;
end;

// edycja
procedure TForm11.Button2Click(Sender: TObject);
var
  key_pr : integer;
  key_gr : integer;
begin
  form12.Caption:='Edycja pracownika';

  // pobranie klucza aktualnego pracownika
  key_pr:=ibq1.FieldByName('pracownik_idx').AsInteger;
  key_gr:=ibq1.FieldByName('prac_grup_idx').AsInteger;

  // ustawienie kontrolek combobox
  Fill_ListaGrup();
  if ibq2.Active then ibq2.Close;
  ibq2.SQL.Clear;
  ibq2.SQL.Add('select * from prac_grup where prac_grup_idx = '+IntToStr(key_gr));
  try
    ibq2.Transaction.StartTransaction;
    ibq2.Open;
    form12.ComboBox1.Text:=ibq2.FieldByName('nazwadl').AsString+' ('+IntToStr(ibq2.FieldByName('prac_grup_idx').AsInteger)+')';
    ibq2.Transaction.Commit;
  except
    ibq2.Transaction.Rollback;
  end;


  form12.Edit1.Text:=ibq1.FieldByName('ean').AsString;
  form12.Edit1.Enabled:=false;
  form12.Edit2.Text:=ibq1.FieldByName('Pracownik').AsString;
  form12.ActiveControl:=form12.Edit1;
  if form12.ShowModal = mrOK then
  begin
    if ibq2.Active then ibq1.Close;
    ibq2.SQL.Clear;
    ibq2.SQL.Add('update pracownik set nazwadl = :a, prac_grup_idx = :c where pracownik_idx = :b');
    ibq2.ParamByName('a').AsString:=form12.Edit2.Text;
    ibq2.ParamByName('b').AsInteger:=key_pr;
    ibq2.ParamByName('c').AsInteger:=GetIdx(form12.ComboBox1.Text);
    try
      ibq2.Transaction.StartTransaction;
      ibq2.ExecSQL;
      ibq2.Transaction.Commit;
    except
      ibq2.Transaction.Rollback;
    end;
    FormShow(nil);
  end;

end;

procedure TForm11.Button3Click(Sender: TObject);
var
  key : integer;
begin
  key:=ibq1.FieldByName('pracownik_idx').AsInteger;
  if form5.ShowModal = mrOK then
  begin
    if ibq1.Active then ibq1.Close;
    ibq2.SQL.Clear;
    ibq2.SQL.Add('delete from pracownik where pracownik_idx = :a');
    ibq2.ParamByName('a').AsInteger:=key;
    try
      ibq2.Transaction.StartTransaction;
      ibq2.ExecSQL;
      ibq2.Transaction.Commit;
    except
      ibq2.Transaction.Rollback;
    end;
    FormShow(nil);
  end;
end;

end.

